package server;

import common.Email;
import common.LoginPassword;
import common.User;

import java.sql.*;
import java.sql.Date;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: Igor Demidov
 * Date: 25.02.13
 * Time: 15:34
 */
public class DataAccessObject {
    private static Connection connection = null;
    public DataAccessObject() throws SQLException {
        try {
            // Название драйвера
            String driverName = "com.mysql.jdbc.Driver";

            Class.forName(driverName);

            // Create a connection to the database
            String serverName = "localhost";
            String mydatabase = "mailprojectdb";
            String url = "jdbc:mysql://" + serverName + "/" + mydatabase;
            String username = "root";
            String password = "1234";

            connection = DriverManager.getConnection(url, username, password);
            System.out.println("is connect to DB" + connection);
            //String query = "Select * FROM football WHERE football.Number = 10";
            //Statement stmt = connection.createStatement();

            //ResultSet rs = stmt.executeQuery(query);
            //String dbtime;
            //while (rs.next()) {
            //    System.out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3));
            //} // end while

        } // end try
        catch (ClassNotFoundException e) {
            e.printStackTrace();
            // Could not find the database driver
        } catch (SQLException e) {
            e.printStackTrace();
            // Could not connect to the database
        }
    }

    private static User findUser(String login) throws SQLException {
        PreparedStatement findUserStmt = connection.prepareStatement("SELECT * FROM users WHERE users.login=?");
        findUserStmt.setString(1, login);
        ResultSet rs = findUserStmt.executeQuery();
        if (rs.next()) {
            String name = rs.getString("name");
            String surname = rs.getString("surname");
            Date registrationDate = java.sql.Date.valueOf(new java.sql.Date(rs.getLong("registrationdate")).toString());
            String phone = rs.getString("phone");
            String userlogin = rs.getString("login");
            String password = rs.getString("password");
            return new User(name, surname, registrationDate, phone, new LoginPassword(userlogin, password));
        } else {
            return null;
        }
    }

    public static boolean tryLogin(LoginPassword loginPassword) throws SQLException {
        User user = findUser(loginPassword.getLogin());
        if (user != null && user.getLoginPassword().getPassword().equals(loginPassword.getPassword())) {
            return true;
        } else {
            return false;
        }
    }

    public static boolean tryRegister(User user) throws SQLException {
        if (findUser(user.getLoginPassword().getLogin()) == null) {
            PreparedStatement preparedStatement = connection.prepareStatement(
            "INSERT INTO users SET name=?, surname=?, registrationdate=?, phone=?, login=?, password=?");
            preparedStatement.setString(1, user.getName());
            preparedStatement.setString(2, user.getSurname());
            preparedStatement.setLong(3, user.getRegistrationDate().getTime());
            preparedStatement.setString(4, user.getPhone());
            preparedStatement.setString(5, user.getLoginPassword().getLogin());
            preparedStatement.setString(6, user.getLoginPassword().getPassword());
            preparedStatement.executeUpdate();
            return true;
        } else {
            return false;
        }
    }

    public static List<Email> getInbox(String userLogin) throws SQLException {
        User user = findUser(userLogin);
        if (user != null) {
            PreparedStatement findUserStmt = connection.prepareStatement(
                    "SELECT * FROM emails JOIN users ON emails.to=users.login WHERE users.login=?");
            findUserStmt.setString(1, userLogin);
            ResultSet rs = findUserStmt.executeQuery();
            List<Email> result = new LinkedList<Email>();
            while (rs.next()) {
                String from = rs.getString("from");
                String to = rs.getString("to");
                Date creationDate = java.sql.Date.valueOf(new java.sql.Date(rs.getLong("creationdate")).toString());
                String topic = rs.getString("topic");
                String body = rs.getString("body");
                result.add(new Email(from, to, creationDate, topic, body));
            }
            return result;
        } else {
            return null;
        }
    }

    public static List<Email> getOutbox(String userLogin) throws SQLException {
        User user = findUser(userLogin);
        if (user != null) {
            PreparedStatement findUserStmt = connection.prepareStatement(
                    "SELECT * FROM emails JOIN users ON emails.from=users.login WHERE users.login=?");
            findUserStmt.setString(1, userLogin);
            ResultSet rs = findUserStmt.executeQuery();
            List<Email> result = new LinkedList<Email>();
            while (rs.next()) {
                String from = rs.getString("from");
                String to = rs.getString("to");
                Date creationDate = java.sql.Date.valueOf(new java.sql.Date(rs.getLong("creationdate")).toString());
                String topic = rs.getString("topic");
                String body = rs.getString("body");
                result.add(new Email(from, to, creationDate, topic, body));
            }
            return result;
        } else {
            return null;
        }
    }

    public static boolean addEmail(Email email) throws SQLException {
        User from = findUser(email.getFrom());
        User to = findUser(email.getTo());
        //System.err.println(from.getLoginPassword().getLogin());
        //System.err.println(to.getLoginPassword().getLogin());
        //System.err.println(email.getTopic());
        //System.err.println(email.getBody());
        if (from != null && to != null) {
            PreparedStatement preparedStatement = connection.prepareStatement(
            "INSERT INTO emails VALUES(?, ?, ?, ?, ?)");
            preparedStatement.setString(1, email.getFrom());
            preparedStatement.setString(2, email.getTo());
            preparedStatement.setLong(3, email.getCreationDate().getTime());
            preparedStatement.setString(4, email.getTopic());
            preparedStatement.setString(5, email.getBody());
            preparedStatement.executeUpdate();
            return true;
        } else {
            return false;
        }
    }
}
